﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using OfficeOpenXml;

using MvcAtletIS.Models;

namespace MvcAtletIS.Areas.Admin.Controllers
{
    public class ExportController : Controller
    {
        atletisEntities db = new atletisEntities();

        // GET: /Export/
        public ActionResult Index()
        {
            return View();
        }

        // GET: /Export/Export/2
        public void Export(int competitionId)
        {
            ExcelPackage pkg = new ExcelPackage(); 
            pkg.Encryption.Password = "AtletisExport" + DateTime.Now.Year.ToString();
            
            // Competition
            ExcelWorksheet wsCompetition = pkg.Workbook.Worksheets.Add("Competition");
            this.ExportCompetition(competitionId, wsCompetition);

            // Discipline List
            ExcelWorksheet wsDisciplineList = pkg.Workbook.Worksheets.Add("DisciplineList");
            this.ExportDisciplineList(competitionId, wsDisciplineList);

            // Check Points
            ExcelWorksheet wsCheckPoints = pkg.Workbook.Worksheets.Add("CheckPoints");
            this.ExportCheckPoints(competitionId, wsCheckPoints);

            // Performances
            ExcelWorksheet wsPerformances = pkg.Workbook.Worksheets.Add("Performances");
            this.ExportPerformances(competitionId, wsPerformances);

            // Categories
            ExcelWorksheet wsCategories = pkg.Workbook.Worksheets.Add("Categories");
            this.ExportCategories(wsCategories);

            // Disciplines
            ExcelWorksheet wsDisciplines = pkg.Workbook.Worksheets.Add("Disciplines");
            this.ExportDisciplines(wsDisciplines);

            // Countries
            ExcelWorksheet wsCountries = pkg.Workbook.Worksheets.Add("Countries");
            this.ExportCountries(wsCountries);

            // Clubs
            ExcelWorksheet wsClubs = pkg.Workbook.Worksheets.Add("Clubs");
            this.ExportClubs(wsClubs);

            // Competitors
            ExcelWorksheet wsCompetitors = pkg.Workbook.Worksheets.Add("Competitors");
            this.ExportCompetitors(wsCompetitors);

            // Response
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=AtletIS_Export.xlsx");
            Response.BinaryWrite(pkg.GetAsByteArray());
        }

        private void ExportPerformances(int competitionId, ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "DisciplineListId";
            ws.Cells["C1"].Value = "CompetitorId";
            ws.Cells["D1"].Value = "FirstName";
            ws.Cells["E1"].Value = "Surname";
            ws.Cells["F1"].Value = "Birthdate";
            ws.Cells["G1"].Value = "Club";
            ws.Cells["H1"].Value = "CountryId";
            ws.Cells["I1"].Value = "StartNumber";
            ws.Cells["J1"].Value = "SB";
            ws.Cells["K1"].Value = "SMS";
            ws.Cells["A1:K1"].Style.Font.Bold = true;

            var performances = db.Performances.Where(p => p.DisciplineList.CompetitionId == competitionId).ToList();
            int row = 2;
            foreach (var item in performances)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.DisciplineListId;
                ws.Cells["C" + row.ToString()].Value = item.CompetitorId;
                ws.Cells["D" + row.ToString()].Value = item.FirstName;
                ws.Cells["E" + row.ToString()].Value = item.Surname;
                ws.Cells["F" + row.ToString()].Value = Convert.ToDateTime(item.Birthdate).ToString("dd.MM.yyyy");
                ws.Cells["G" + row.ToString()].Value = item.Club;
                ws.Cells["H" + row.ToString()].Value = item.CountryId;
                ws.Cells["I" + row.ToString()].Value = item.StartNumber;
                ws.Cells["J" + row.ToString()].Value = item.SB;
                ws.Cells["K" + row.ToString()].Value = item.SMS;

                row += 1;
            }
        }
        
        private void ExportCheckPoints(int competitionId, ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "DisciplineListId";
            ws.Cells["C1"].Value = "OrderInDiscipline";
            ws.Cells["D1"].Value = "Name";
            ws.Cells["E1"].Value = "Code";
            ws.Cells["F1"].Value = "PcId";
            ws.Cells["G1"].Value = "LapCount";
            ws.Cells["A1:G1"].Style.Font.Bold = true;

            var checkPoints = db.CheckPoints.Where(ch => ch.DisciplineList.CompetitionId == competitionId).ToList();
            int row = 2;
            foreach (var item in checkPoints)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.DisciplineListId;
                ws.Cells["C" + row.ToString()].Value = item.OrderInDiscipline;
                ws.Cells["D" + row.ToString()].Value = item.Name;
                ws.Cells["E" + row.ToString()].Value = item.Code;
                ws.Cells["F" + row.ToString()].Value = item.PcId;
                ws.Cells["G" + row.ToString()].Value = item.LapCount;

                row += 1;
            }
        }

        private void ExportDisciplineList(int competitionId, ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "ExternalId";
            ws.Cells["D1"].Value = "CompetitionId";
            ws.Cells["E1"].Value = "DisciplineId";
            ws.Cells["F1"].Value = "CategoryId";
            ws.Cells["G1"].Value = "StartTime";
            ws.Cells["H1"].Value = "DisciplineCode";
            ws.Cells["A1:H1"].Style.Font.Bold = true;

            var disciplineList = db.DisciplineList.Where(dl => dl.CompetitionId == competitionId).ToList();
            int row = 2;
            foreach (var item in disciplineList)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.Name;
                ws.Cells["C" + row.ToString()].Value = item.ExternalId;
                ws.Cells["D" + row.ToString()].Value = item.CompetitionId;
                ws.Cells["E" + row.ToString()].Value = item.DisciplineId;
                ws.Cells["F" + row.ToString()].Value = item.CategoryId;
                ws.Cells["G" + row.ToString()].Value = item.StartTime;
                ws.Cells["H" + row.ToString()].Value = item.DisciplineCode;

                row += 1;
            }
        }

        private void ExportCompetition(int competitionId, ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "City";
            ws.Cells["D1"].Value = "Organiser";
            ws.Cells["E1"].Value = "DateFrom";
            ws.Cells["F1"].Value = "DateTo";
            ws.Cells["G1"].Value = "Deadline";
            ws.Cells["H1"].Value = "ClubsCountries";
            ws.Cells["I1"].Value = "Lanes";
            ws.Cells["J1"].Value = "LanesQuality";
            ws.Cells["A1:J1"].Style.Font.Bold = true;

            var competition = db.Competitions.Single(c => c.ID == competitionId);
            ws.Cells["A2"].Value = competition.ID;
            ws.Cells["B2"].Value = competition.Name;
            ws.Cells["C2"].Value = competition.City;
            ws.Cells["D2"].Value = competition.Organiser;
            ws.Cells["E2"].Value = Convert.ToDateTime(competition.DateFrom).ToString("dd.MM.yyyy");
            ws.Cells["F2"].Value = Convert.ToDateTime(competition.DateTo).ToString("dd.MM.yyyy");
            ws.Cells["G2"].Value = Convert.ToDateTime(competition.Deadline).ToString("dd.MM.yyyy");
            ws.Cells["H2"].Value = competition.ClubsCountries;
            ws.Cells["I2"].Value = competition.Lanes;
            ws.Cells["J2"].Value = competition.LanesQuality;
        }

        private void ExportCategories(ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "AlterName";
            ws.Cells["D1"].Value = "Sex";
            ws.Cells["E1"].Value = "YearFrom";
            ws.Cells["F1"].Value = "YearTo";
            ws.Cells["G1"].Value = "Points";
            ws.Cells["H1"].Value = "Active";
            ws.Cells["A1:H1"].Style.Font.Bold = true;

            var categories = db.Categories.ToList();
            int row = 2;
            foreach (var item in categories)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.Name;
                ws.Cells["C" + row.ToString()].Value = item.AlterName;
                ws.Cells["D" + row.ToString()].Value = item.Sex;
                ws.Cells["E" + row.ToString()].Value = item.YearFrom;
                ws.Cells["F" + row.ToString()].Value = item.YearTo;
                ws.Cells["G" + row.ToString()].Value = item.Points;
                ws.Cells["H" + row.ToString()].Value = item.Active;

                row += 1;
            }

        }

        private void ExportDisciplines(ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "AlterName";
            ws.Cells["D1"].Value = "Description";
            ws.Cells["E1"].Value = "Code";
            ws.Cells["F1"].Value = "ResultCode";
            ws.Cells["G1"].Value = "Active";
            ws.Cells["A1:G1"].Style.Font.Bold = true;

            var disciplines = db.Disciplines.ToList();
            int row = 2;
            foreach (var item in disciplines)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.Name;
                ws.Cells["C" + row.ToString()].Value = item.AlterName;
                ws.Cells["D" + row.ToString()].Value = item.Description;
                ws.Cells["E" + row.ToString()].Value = item.Code;
                ws.Cells["F" + row.ToString()].Value = item.ResultCode;
                ws.Cells["G" + row.ToString()].Value = item.Active;

                row += 1;
            }
        }

        private void ExportCountries(ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["A1:B1"].Style.Font.Bold = true;

            var countries = db.Countries.ToList();
            int row = 2;
            foreach (var item in countries)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.Name;

                row += 1;
            }
        }

        private void ExportClubs(ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Name";
            ws.Cells["C1"].Value = "City";
            ws.Cells["D1"].Value = "Active";
            ws.Cells["A1:D1"].Style.Font.Bold = true;

            var clubs = db.Clubs.ToList();
            int row = 2;
            foreach (var item in clubs)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.Name;
                ws.Cells["C" + row.ToString()].Value = item.City;
                ws.Cells["D" + row.ToString()].Value = item.Active;

                row += 1;
            }
        }

        private void ExportCompetitors(ExcelWorksheet ws)
        {
            // Header
            ws.Cells["A1"].Value = "ID";
            ws.Cells["B1"].Value = "Firstname";
            ws.Cells["C1"].Value = "Surname";
            ws.Cells["D1"].Value = "Birthdate";
            ws.Cells["E1"].Value = "Sex";
            ws.Cells["F1"].Value = "ClubId";
            ws.Cells["G1"].Value = "CountryId";
            ws.Cells["H1"].Value = "Competitor";
            ws.Cells["I1"].Value = "Active";
            ws.Cells["A1:I1"].Style.Font.Bold = true;

            var competitors = db.Competitors.ToList();
            int row = 2;
            foreach (var item in competitors)
            {
                ws.Cells["A" + row.ToString()].Value = item.ID;
                ws.Cells["B" + row.ToString()].Value = item.Firstname;
                ws.Cells["C" + row.ToString()].Value = item.Surname;
                ws.Cells["D" + row.ToString()].Value = Convert.ToDateTime(item.Birthdate).ToString("dd.MM.yyyy");
                ws.Cells["E" + row.ToString()].Value = item.Sex;
                ws.Cells["F" + row.ToString()].Value = item.ClubId;
                ws.Cells["G" + row.ToString()].Value = item.CountryId;
                ws.Cells["H" + row.ToString()].Value = item.Competitor;
                ws.Cells["I" + row.ToString()].Value = item.Active;

                row += 1;
            }
        }



    }
}
